package com.infore.maintenance.main.utils.parsesql;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.google.common.collect.Maps;
import com.infore.common.enums.ExecuteSql;
import com.infore.common.exception.MyException;
import com.infore.common.utils.StringUtils;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.SimpleNode;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;

import java.io.StringReader;
import java.util.*;

public class SqlAnalysis {


    /**
     * 防止SQL注入
     * @param executeSql
     * @param type
     * @return
     */
    public static boolean preventSqlInjection(String executeSql, ExecuteSql type){
        String  executeSqlUp= executeSql.trim().toUpperCase();
        //可以替换大部分空白字符， 不限于空格
        executeSqlUp = executeSqlUp.replaceAll("\\s*", "");
        if(ExecuteSql.SELECT.equals(type)){
            if (executeSqlUp.indexOf("SELECT") != 0) {
                throw new MyException("500","执行SQL只能是以SELECT开头的查询语句");
            }
        }else  if(ExecuteSql.INSERT.equals(type)){
            if(executeSqlUp.indexOf("INSERT") != 0){
                throw new MyException("500","执行SQL只能是以INSERT开头的语句");
            }
        }else  if(ExecuteSql.UPDATE.equals(type)){
            if(executeSqlUp.indexOf("UPDATE") != 0){
                throw new MyException("500","执行SQL只能是以UPDATE开头的语句");
            }
        }else  if(ExecuteSql.DELETE.equals(type)){
            if(executeSqlUp.indexOf("DELETE") != 0){
                throw new MyException("500","执行SQL只能是以DELETE开头的语句");
            }
        }
        if( executeSqlUp.contains("DROPDATABASE") ||  executeSqlUp.contains("DROPTABLE") || executeSqlUp.contains("TRUNCATETABLE")|| executeSqlUp.contains("ALTERTABLE")){
            throw new MyException("500","执行SQL中不能出现关键词【DROP DATABASE|DROP TABLE|TRUNCATE TABLE|ALTER TABLE】");
        }
        return true;

    }


    /**
     * 获取各列的单位
     * @param resultUnit
     * @return
     */
    public static Map<String,String> getUnit(String resultUnit){
        Map<String,String> result = Maps.newHashMap();
        if(StringUtils.isNotEmpty(resultUnit)){
            String[] sz = resultUnit.split(",");
            for (int i = 0; i <sz.length ; i++) {
                String[] keyVal = sz[i].split("\\|");
                if(keyVal.length==2){
                    result.put(keyVal[0],keyVal[1]);
                }{
                    throw new MyException("500","result_unit格式不正确");
                }
            }
        }
        return result;
    }

    /**
     * 获取列头
     * @param resultColumn
     * @return
     */
    public static List<String> getHead(String resultColumn){
        List<String> list =new ArrayList<>();
        if(StringUtils.isNotEmpty(resultColumn)){
            String[] sz = resultColumn.split(",");
            for (int i = 0; i <sz.length ; i++) {
                String[] keyVal = sz[i].split("\\|");
                if(keyVal.length==2){
                    list.add(keyVal[1]);
                }else{
                    throw new MyException("500","result_column格式不正确");
                }
            }
        }
        return list;
    }
    /**
     * 获取列
     * @param resultColumn
     * @return
     */
    public static List<String> getCol(String resultColumn){
        List<String> list =new ArrayList<>();
        if(StringUtils.isNotEmpty(resultColumn)){
            String[] sz = resultColumn.split(",");
            for (int i = 0; i <sz.length ; i++) {
                String[] keyVal = sz[i].split("\\|");
                if(keyVal.length==2){
                    list.add(keyVal[0]);
                }else{
                    throw new MyException("500","result_column格式不正确");
                }
            }
        }
        return list;
    }

    /**
     * 校验SELECT查询结果列与resultColumn返回结果列是否匹配
     * @param sql
     * @param resultColumn
     * @return
     */
    public static List<String> matchSelectSQLAndResultColumn(String sql,String resultColumn){
        List<String> selectCol = analyzeSelectSQL(sql);
        if(selectCol==null || selectCol.size()==0){
            throw new MyException("500","SELECT查询结果列名为空");
        }
        List<String> headCol = getCol(resultColumn);
        if(headCol==null || headCol.size()==0){
            throw new MyException("500","resultColumn返回结果列为空");
        }
        if(selectCol.size()==1 && selectCol.get(0).contains("*")){
            return headCol;
        }else{
            if(selectCol.size()!=headCol.size()){
                throw new MyException("500","SELECT查询结果列与resultColumn返回结果列数量不一致");
            }
            for(String col :selectCol){
                if(!headCol.contains(col)){
                    throw new MyException("500","SELECT查询结果列与resultColumn返回结果列不匹配");
                }
            }
        }
        return headCol;
    }


    /**
     * 解析sql语句中的列名
     * @param sql
     * @return
     */
    public static List<String> analyzeSelectSQL(String sql){
        sql = TextSqlNode.replaceSqlPlaceholder(sql);
        System.out.println(sql);
        List<String> result = new ArrayList<String>();
        try {
            CCJSqlParserManager pm = new CCJSqlParserManager();
            Statement statement = pm.parse(new StringReader(sql));
            if (statement instanceof Select) {
                Select selectStatement = (Select) statement;
                PlainSelect selectBody = (PlainSelect) selectStatement.getSelectBody();
                List<SelectItem> selectItemlist = selectBody.getSelectItems();
                SelectItem selectItem = null;
                SelectExpressionItem selectExpressionItem = null;
                AllTableColumns allTableColumns = null;
                Alias alias = null;
                SimpleNode node = null;
                if (selectItemlist != null) {
                    for (int i = 0; i < selectItemlist.size(); i++) {
                        selectItem = selectItemlist.get(i);
                        if (selectItem instanceof SelectExpressionItem) {
                            selectExpressionItem = (SelectExpressionItem) selectItemlist.get(i);
                            alias = selectExpressionItem.getAlias();
                            node = selectExpressionItem.getExpression().getASTNode();
                            Object value = node.jjtGetValue();
                            String columnName = "";
                            if (value instanceof Column) {
                                columnName = ((Column) value).getColumnName();
                            } else if (value instanceof Function) {
                                columnName = ((Function) value).toString();
                            }else {
                                // 增加对select 'aaa' from table; 的支持
//                                columnName = ObjectUtils.praseObjectToString(value);
                                columnName = String.valueOf(value);
                                columnName = columnName.replace("'", "");
                                columnName = columnName.replace("\"", "");
                            }
                            if (alias != null) {
                                columnName = alias.getName();
                                //去掉别名的双引号
                                columnName = columnName.replace("\"","");
                            }
                            result.add(columnName);
                        } else if (selectItem instanceof AllTableColumns) {
                            allTableColumns = (AllTableColumns) selectItemlist.get(i);
                            result.add(allTableColumns.toString());
                        } else {
                            result.add(selectItem.toString());
                        }
                    }
                }
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        return result;
    }


    /**
     * 组装SQL
     * @param executeSql 执行SQL模板
     * @param queryParams 模板配置的查询参数
     * @param jsonParam 前端传的Json参数值
     * @return
     */
    public static String assembleSql(String executeSql, String  queryParams,String jsonParam){
        System.out.println("sql demo:"+executeSql);
        if(StringUtils.isNotEmpty(queryParams)){
            //模板中含有的参数
            List<String> params = Arrays.asList(queryParams.split(","));
            JSONObject frontJsonObj = null;
            if(StringUtils.isNotEmpty(jsonParam)){
                frontJsonObj = JSONUtil.parseObj(jsonParam);
                //判断参数是否符合传参要求
                Iterator sIterator = frontJsonObj.keySet().iterator();
                while (sIterator.hasNext()) {
                    // 获得JSON参数的key
                    String key = sIterator.next().toString();
                    System.out.println("未知key:"+key);
                    if(!params.contains(key)){
                        throw new MyException("500","当前模板不需要参数:"+key);
                    }
                }
            }else{
                frontJsonObj = new JSONObject();
            }
            for (String par:params) {
                Object val = frontJsonObj.get(par);
                if(val==null){
                    executeSql = executeSql.replaceAll(":"+par,"null");
                    continue;
                }
                if(val instanceof String){
                    System.out.println("字符串类型");
                    System.out.println(":"+par);
                    executeSql = executeSql.replaceAll(":"+par,"'"+val+"'");
                }else if(val instanceof JSONArray){
                    System.out.println("JSON数组");
                    StringBuilder sbIn = new StringBuilder();
                    JSONArray jsonArray = frontJsonObj.getJSONArray(par);
                    Object[] objects = jsonArray.toArray();
                    for (int i = 0; i <objects.length ; i++) {
                        if (i == objects.length - 1) {
                            sbIn.append("'").append(objects[i]).append("'");
                        } else {
                            sbIn.append("'").append(objects[i]).append("'").append(",");
                        }
                    }
                    executeSql = executeSql.replaceAll(":"+par,sbIn.toString());
                }else if(val instanceof Double || val instanceof Float ||val instanceof Long || val instanceof Integer){
                    System.out.println("数字类型");
                    executeSql = executeSql.replaceAll(":"+par,val.toString());
                }else {
                    executeSql = executeSql.replaceAll(":"+par,val.toString());
                }
            }
        }else {
            if(StringUtils.isNotEmpty(jsonParam)){
                throw new MyException("500","当前SQL执行不需要JSON参数");
            }
        }
        return  executeSql;
    }

    public static void main(String[] args) {
//        String sql1 = "select u.* from soa_sys_user u";
//        List<String> column1 = analyzeSelectSQL(sql1);
//        System.out.println("sql1的解析结果如下：");
//        System.out.println(JSONUtil.toJsonStr(column1));
//        System.out.println("");
        System.out.println("sql2的解析结果如下：");
//        String sql2 = "select u.user_id, user_name as userName, email, weixin, to_date(sysdate, 'yyyy-mm-dd hh24:mi:ss') as date from soa_sys_user u";
        String sql2 = "select PROJECT_ID as \"project_id\",PROJECT_NAME as \"project_name\"\n" +
                "from basic_db.t_basic_rule_detail_oth\n" +
                "where (#{xx} is null or  RULE_ID=${xx})";
        List<String> column2 = analyzeSelectSQL(sql2);
        System.out.println(JSONUtil.toJsonStr(column2));
    }
}
